﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Maticsoft.DBUtility;
using System.Data.SqlClient;
using System.Data;

namespace EnterpriseFund.DAL
{
    public class SB_ZSXX
    {
        private string SZWHERE
        {
            get
            {
                string str = System.Configuration.ConfigurationManager.AppSettings["SZWHERE"];
                if (str != "")
                {
                    return System.Configuration.ConfigurationManager.AppSettings["SZWHERE"];
                }
                else
                {
                    return "";
                }
            }
        }

        public void ImportData(string strwhere = "1=1", string strwhere2 = "1=1", string orastrwhere = "1=1", string orastrwhere2 = "1=1", int outTimes = 60*60)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> commandInfoList = new List<CommandInfo>();
            CommandInfo commandInfo = new CommandInfo();
            SqlParameter[] parameters = new SqlParameter[] { };

            SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString);
            conn.Open();
            SqlTransaction tran = conn.BeginTransaction();
            SqlCommand command = new SqlCommand();
            command.CommandTimeout = outTimes;

            string a = "";
            int teml2 = 0;
            try
            {

                strSql = new StringBuilder();
                strSql.Append("delete from ltais.SB_ZSXX where " + strwhere);
                command = new SqlCommand(strSql.ToString(), conn, tran);
                command.CommandTimeout = outTimes;
                command.ExecuteNonQuery();

                strSql = new StringBuilder();
                strSql.Append("delete from  ltais.SB_TTXX where " + strwhere2);
                command = new SqlCommand(strSql.ToString(), conn, tran);
                command.CommandTimeout = outTimes;
                command.ExecuteNonQuery();


                strSql = new StringBuilder();
                strSql.Append("select count(*) ");
                strSql.Append(" from ltais.dj_nsrxx a, ltais.sb_zsxx b where " + orastrwhere);
                object count = DbHelperOra.GetSingle(strSql.ToString());
                int sumCount = 0;
                int temp = 0;
                a = "sb_zsxx";
                if (count != null)
                {
                    sumCount = int.Parse(count.ToString());
                }
                for (int i = 0; i < sumCount / 5000; i++)
                {
                    DataSet ds = new DataSet();
                    strSql = new StringBuilder();
                    strSql.Append("select ZSXH,NSRDZDAH,SSSQ_Q,SSSQ_Z,ZSXM_DM,ZSPM_DM,SKZL_DM,SKSX_DM,SKZT_DM,SKCSFS_DM,QLQQSLX_DM,TZLX_DM,TZRQ,KSSL,XSSR,SL,SE,YZPZXH,YZPZMXXH,YZPZZL_DM,YZPZLRR_DM,YZFSRQ,YZFSRQ_JZ,JKQX,JKPZXH,JKPZZL_DM,JKPZLRR_DM,KPRQ,YSSPHM,JKFSRQ,JKFSRQ_JZ,JKFS_DM,KKLX_DM,HZJKSH,SJRQ,SJRQ_JZ,SJXHR_DM,SJXHRQ,RKRQ,RKRQ_JZ,RKXHR_DM,RKXHRQ,DJZCLX_DM,HY_DM,SBFS_DM,ZSFS_DM,ZZSNSRLX_DM,YSKM_DM,YSFPBL_DM,YSFPBL_ZY,YSFPBL_SS,YSFPBL_DS,YSFPBL_XQ,YSFPBL_XZ,YSFPBL_XC,SKGK_DM,JDXZ_DM,ZGSWRY_DM,SKSS_SWJG_DM,ZSJG_DM,NSR_SWJG_DM,SWJG_DM,LRRQ,LRR_DM,XGRQ,XGR_DM,BZ from ( select b.ZSXH,b.NSRDZDAH,b.SSSQ_Q,b.SSSQ_Z,b.ZSXM_DM,b.ZSPM_DM,b.SKZL_DM,b.SKSX_DM,b.SKZT_DM,b.SKCSFS_DM,b.QLQQSLX_DM,b.TZLX_DM,b.TZRQ,b.KSSL,b.XSSR,b.SL,b.SE,b.YZPZXH,b.YZPZMXXH,b.YZPZZL_DM,b.YZPZLRR_DM,b.YZFSRQ,b.YZFSRQ_JZ,b.JKQX,b.JKPZXH,b.JKPZZL_DM,b.JKPZLRR_DM,b.KPRQ,b.YSSPHM,b.JKFSRQ,b.JKFSRQ_JZ,b.JKFS_DM,b.KKLX_DM,b.HZJKSH,b.SJRQ,b.SJRQ_JZ,b.SJXHR_DM,b.SJXHRQ,b.RKRQ,b.RKRQ_JZ,b.RKXHR_DM,b.RKXHRQ,b.DJZCLX_DM,b.HY_DM,b.SBFS_DM,b.ZSFS_DM,b.ZZSNSRLX_DM,b.YSKM_DM,b.YSFPBL_DM,b.YSFPBL_ZY,b.YSFPBL_SS,b.YSFPBL_DS,b.YSFPBL_XQ,b.YSFPBL_XZ,b.YSFPBL_XC,b.SKGK_DM,b.JDXZ_DM,b.ZGSWRY_DM,b.SKSS_SWJG_DM,b.ZSJG_DM,b.NSR_SWJG_DM,b.SWJG_DM,b.LRRQ,b.LRR_DM,b.XGRQ,b.XGR_DM,b.BZ, rownum r ");
                    strSql.Append(" from ltais.dj_nsrxx a, ltais.sb_zsxx b where " + orastrwhere + ") where r >=" + (i * 5000 + 1) + " and r <=" + ((i + 1) * 5000));
                    ds = DbHelperOra.Query(strSql.ToString());

                    SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);
                    sqlbulkcopy.DestinationTableName = "ltais.SB_ZSXX";//数据库中的表名
                    sqlbulkcopy.BulkCopyTimeout = outTimes;
                    sqlbulkcopy.WriteToServer(ds.Tables[0]);
                    temp = ((i + 1) * 5000);
                    teml2 = temp;
                }

                if (sumCount < 5000)
                {
                    DataSet ds = new DataSet();
                    strSql = new StringBuilder();
                    strSql.Append("select ZSXH,NSRDZDAH,SSSQ_Q,SSSQ_Z,ZSXM_DM,ZSPM_DM,SKZL_DM,SKSX_DM,SKZT_DM,SKCSFS_DM,QLQQSLX_DM,TZLX_DM,TZRQ,KSSL,XSSR,SL,SE,YZPZXH,YZPZMXXH,YZPZZL_DM,YZPZLRR_DM,YZFSRQ,YZFSRQ_JZ,JKQX,JKPZXH,JKPZZL_DM,JKPZLRR_DM,KPRQ,YSSPHM,JKFSRQ,JKFSRQ_JZ,JKFS_DM,KKLX_DM,HZJKSH,SJRQ,SJRQ_JZ,SJXHR_DM,SJXHRQ,RKRQ,RKRQ_JZ,RKXHR_DM,RKXHRQ,DJZCLX_DM,HY_DM,SBFS_DM,ZSFS_DM,ZZSNSRLX_DM,YSKM_DM,YSFPBL_DM,YSFPBL_ZY,YSFPBL_SS,YSFPBL_DS,YSFPBL_XQ,YSFPBL_XZ,YSFPBL_XC,SKGK_DM,JDXZ_DM,ZGSWRY_DM,SKSS_SWJG_DM,ZSJG_DM,NSR_SWJG_DM,SWJG_DM,LRRQ,LRR_DM,XGRQ,XGR_DM,BZ from ( select b.ZSXH,b.NSRDZDAH,b.SSSQ_Q,b.SSSQ_Z,b.ZSXM_DM,b.ZSPM_DM,b.SKZL_DM,b.SKSX_DM,b.SKZT_DM,b.SKCSFS_DM,b.QLQQSLX_DM,b.TZLX_DM,b.TZRQ,b.KSSL,b.XSSR,b.SL,b.SE,b.YZPZXH,b.YZPZMXXH,b.YZPZZL_DM,b.YZPZLRR_DM,b.YZFSRQ,b.YZFSRQ_JZ,b.JKQX,b.JKPZXH,b.JKPZZL_DM,b.JKPZLRR_DM,b.KPRQ,b.YSSPHM,b.JKFSRQ,b.JKFSRQ_JZ,b.JKFS_DM,b.KKLX_DM,b.HZJKSH,b.SJRQ,b.SJRQ_JZ,b.SJXHR_DM,b.SJXHRQ,b.RKRQ,b.RKRQ_JZ,b.RKXHR_DM,b.RKXHRQ,b.DJZCLX_DM,b.HY_DM,b.SBFS_DM,b.ZSFS_DM,b.ZZSNSRLX_DM,b.YSKM_DM,b.YSFPBL_DM,b.YSFPBL_ZY,b.YSFPBL_SS,b.YSFPBL_DS,b.YSFPBL_XQ,b.YSFPBL_XZ,b.YSFPBL_XC,b.SKGK_DM,b.JDXZ_DM,b.ZGSWRY_DM,b.SKSS_SWJG_DM,b.ZSJG_DM,b.NSR_SWJG_DM,b.SWJG_DM,b.LRRQ,b.LRR_DM,b.XGRQ,b.XGR_DM,b.BZ, rownum r ");
                    strSql.Append("   from ltais.dj_nsrxx a, ltais.sb_zsxx b where " + orastrwhere + ")");
                    ds = DbHelperOra.Query(strSql.ToString());

                    SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);
                    sqlbulkcopy.DestinationTableName = "ltais.SB_ZSXX";//数据库中的表名
                    sqlbulkcopy.BulkCopyTimeout = outTimes;
                    sqlbulkcopy.WriteToServer(ds.Tables[0]);
                }
                else
                {

                    if (sumCount > temp)
                    {
                        DataSet ds = new DataSet();
                        strSql = new StringBuilder();
                        strSql.Append("select ZSXH,NSRDZDAH,SSSQ_Q,SSSQ_Z,ZSXM_DM,ZSPM_DM,SKZL_DM,SKSX_DM,SKZT_DM,SKCSFS_DM,QLQQSLX_DM,TZLX_DM,TZRQ,KSSL,XSSR,SL,SE,YZPZXH,YZPZMXXH,YZPZZL_DM,YZPZLRR_DM,YZFSRQ,YZFSRQ_JZ,JKQX,JKPZXH,JKPZZL_DM,JKPZLRR_DM,KPRQ,YSSPHM,JKFSRQ,JKFSRQ_JZ,JKFS_DM,KKLX_DM,HZJKSH,SJRQ,SJRQ_JZ,SJXHR_DM,SJXHRQ,RKRQ,RKRQ_JZ,RKXHR_DM,RKXHRQ,DJZCLX_DM,HY_DM,SBFS_DM,ZSFS_DM,ZZSNSRLX_DM,YSKM_DM,YSFPBL_DM,YSFPBL_ZY,YSFPBL_SS,YSFPBL_DS,YSFPBL_XQ,YSFPBL_XZ,YSFPBL_XC,SKGK_DM,JDXZ_DM,ZGSWRY_DM,SKSS_SWJG_DM,ZSJG_DM,NSR_SWJG_DM,SWJG_DM,LRRQ,LRR_DM,XGRQ,XGR_DM,BZ from ( select b.ZSXH,b.NSRDZDAH,b.SSSQ_Q,b.SSSQ_Z,b.ZSXM_DM,b.ZSPM_DM,b.SKZL_DM,b.SKSX_DM,b.SKZT_DM,b.SKCSFS_DM,b.QLQQSLX_DM,b.TZLX_DM,b.TZRQ,b.KSSL,b.XSSR,b.SL,b.SE,b.YZPZXH,b.YZPZMXXH,b.YZPZZL_DM,b.YZPZLRR_DM,b.YZFSRQ,b.YZFSRQ_JZ,b.JKQX,b.JKPZXH,b.JKPZZL_DM,b.JKPZLRR_DM,b.KPRQ,b.YSSPHM,b.JKFSRQ,b.JKFSRQ_JZ,b.JKFS_DM,b.KKLX_DM,b.HZJKSH,b.SJRQ,b.SJRQ_JZ,b.SJXHR_DM,b.SJXHRQ,b.RKRQ,b.RKRQ_JZ,b.RKXHR_DM,b.RKXHRQ,b.DJZCLX_DM,b.HY_DM,b.SBFS_DM,b.ZSFS_DM,b.ZZSNSRLX_DM,b.YSKM_DM,b.YSFPBL_DM,b.YSFPBL_ZY,b.YSFPBL_SS,b.YSFPBL_DS,b.YSFPBL_XQ,b.YSFPBL_XZ,b.YSFPBL_XC,b.SKGK_DM,b.JDXZ_DM,b.ZGSWRY_DM,b.SKSS_SWJG_DM,b.ZSJG_DM,b.NSR_SWJG_DM,b.SWJG_DM,b.LRRQ,b.LRR_DM,b.XGRQ,b.XGR_DM,b.BZ, rownum r ");
                        strSql.Append("   from ltais.dj_nsrxx a, ltais.sb_zsxx b where " + orastrwhere + ") where r >=" + (temp + 1) + " and r <=" + (sumCount));
                        ds = DbHelperOra.Query(strSql.ToString());

                        SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);
                        sqlbulkcopy.DestinationTableName = "ltais.SB_ZSXX";//数据库中的表名
                        sqlbulkcopy.BulkCopyTimeout = outTimes;
                        sqlbulkcopy.WriteToServer(ds.Tables[0]);
                    }
                }

                strSql = new StringBuilder();
                strSql.Append(@"select sum(b.se) ");
                strSql.Append(" from ltais.dj_nsrxx a, ltais.sb_zsxx b where " + orastrwhere);
                object sum = DbHelperOra.GetSingle(strSql.ToString());

                strSql = new StringBuilder();
                strSql.Append(@"select sum(se) ");
                strSql.Append(" from ltais.SB_ZSXX where " + strwhere);

                command = new SqlCommand(strSql.ToString(), conn, tran);
                command.CommandTimeout = outTimes;
                object sum2 = command.ExecuteScalar();
                //object sum2 = DbHelperSQL.GetSingle(strSql.ToString());

                if (sum != null && sum2 != null)
                {

                    if (decimal.Parse(sum.ToString()) != decimal.Parse(sum2.ToString()))
                    {
                        throw new Exception("税款总额不一致");
                    }

                }
                else
                {
                    throw new Exception("税款总额出错");
                }


                strSql = new StringBuilder();
                strSql.Append("select count(*) ");
                strSql.Append(" from ltais.dj_nsrxx a, ltais.sb_ttxx b where " + orastrwhere2);
                count = DbHelperOra.GetSingle(strSql.ToString());
                sumCount = 0;
                a = "sb_ttxx";
                if (count != null)
                {
                    sumCount = int.Parse(count.ToString());
                }

                for (int i = 0; i < sumCount / 5000; i++)
                {
                    DataSet ds = new DataSet();
                    strSql = new StringBuilder();
                    strSql.Append("select TTXH,PZ_DM,XTSPHM,YSSPHM,KDTSKXH,WSPZXH,NSRDZDAH,SSSQ_Q,SSSQ_Z,KPRQ,ZSXM_DM,ZSPM_DM,SKZL_DM,SKSX_DM,SE,PZZL_DM,PZLRR_DM,TTLX_DM,JMYY_DM,JMYJ_DM,CKTSGC_DM,CKTS_QYLX_DM,HGDM,LHJHH,CKTS_ZDDH_BZ,TZLX_DM,TZRQ,DJZCLX_DM,HY_DM,YSKM_DM,YSFPBL_DM,YSFPBL_ZY,YSFPBL_SS,YSFPBL_DS,YSFPBL_XQ,YSFPBL_XZ,YSFPBL_XC,ZZSNSRLX_DM,SKGK_DM,YHZL_DM,YH_DM,YHZH,ZFR_DM,ZFRQ,XHR_DM,XHRQ,THRQ,THRQ_JZ,JDXZ_DM,ZGSWRY_DM,SKSS_SWJG_DM,TTJG_DM,NSR_SWJG_DM,SWJG_DM,LRR_DM,LRRQ,XGR_DM,XGRQ from (select b.TTXH,b.PZ_DM,b.XTSPHM,b.YSSPHM,b.KDTSKXH,b.WSPZXH,b.NSRDZDAH,b.SSSQ_Q,b.SSSQ_Z,b.KPRQ,b.ZSXM_DM,b.ZSPM_DM,b.SKZL_DM,b.SKSX_DM,b.SE,b.PZZL_DM,b.PZLRR_DM,b.TTLX_DM,b.JMYY_DM,b.JMYJ_DM,b.CKTSGC_DM,b.CKTS_QYLX_DM,b.HGDM,b.LHJHH,b.CKTS_ZDDH_BZ,b.TZLX_DM,b.TZRQ,b.DJZCLX_DM,b.HY_DM,b.YSKM_DM,b.YSFPBL_DM,b.YSFPBL_ZY,b.YSFPBL_SS,b.YSFPBL_DS,b.YSFPBL_XQ,b.YSFPBL_XZ,b.YSFPBL_XC,b.ZZSNSRLX_DM,b.SKGK_DM,b.YHZL_DM,b.YH_DM,b.YHZH,b.ZFR_DM,b.ZFRQ,b.XHR_DM,b.XHRQ,b.THRQ,b.THRQ_JZ,b.JDXZ_DM,b.ZGSWRY_DM,b.SKSS_SWJG_DM,b.TTJG_DM,b.NSR_SWJG_DM,b.SWJG_DM,b.LRR_DM,b.LRRQ,b.XGR_DM,b.XGRQ, rownum r  ");
                    strSql.Append("     from ltais.dj_nsrxx a, ltais.sb_ttxx b  where " + orastrwhere2 + ") where r >=" + (i * 5000 + 1) + " and r <=" + ((i + 1) * 5000));
                    ds = DbHelperOra.Query(strSql.ToString());

                    SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);
                    sqlbulkcopy.DestinationTableName = "ltais.SB_TTXX";//数据库中的表名
                    sqlbulkcopy.BulkCopyTimeout = outTimes;
                    sqlbulkcopy.WriteToServer(ds.Tables[0]);

                    temp = ((i + 1) * 5000);

                    teml2 = temp;
                }

                if (sumCount < 5000)
                {
                    DataSet ds = new DataSet();
                    strSql = new StringBuilder();
                    strSql.Append("select TTXH,PZ_DM,XTSPHM,YSSPHM,KDTSKXH,WSPZXH,NSRDZDAH,SSSQ_Q,SSSQ_Z,KPRQ,ZSXM_DM,ZSPM_DM,SKZL_DM,SKSX_DM,SE,PZZL_DM,PZLRR_DM,TTLX_DM,JMYY_DM,JMYJ_DM,CKTSGC_DM,CKTS_QYLX_DM,HGDM,LHJHH,CKTS_ZDDH_BZ,TZLX_DM,TZRQ,DJZCLX_DM,HY_DM,YSKM_DM,YSFPBL_DM,YSFPBL_ZY,YSFPBL_SS,YSFPBL_DS,YSFPBL_XQ,YSFPBL_XZ,YSFPBL_XC,ZZSNSRLX_DM,SKGK_DM,YHZL_DM,YH_DM,YHZH,ZFR_DM,ZFRQ,XHR_DM,XHRQ,THRQ,THRQ_JZ,JDXZ_DM,ZGSWRY_DM,SKSS_SWJG_DM,TTJG_DM,NSR_SWJG_DM,SWJG_DM,LRR_DM,LRRQ,XGR_DM,XGRQ from (select b.TTXH,b.PZ_DM,b.XTSPHM,b.YSSPHM,b.KDTSKXH,b.WSPZXH,b.NSRDZDAH,b.SSSQ_Q,b.SSSQ_Z,b.KPRQ,b.ZSXM_DM,b.ZSPM_DM,b.SKZL_DM,b.SKSX_DM,b.SE,b.PZZL_DM,b.PZLRR_DM,b.TTLX_DM,b.JMYY_DM,b.JMYJ_DM,b.CKTSGC_DM,b.CKTS_QYLX_DM,b.HGDM,b.LHJHH,b.CKTS_ZDDH_BZ,b.TZLX_DM,b.TZRQ,b.DJZCLX_DM,b.HY_DM,b.YSKM_DM,b.YSFPBL_DM,b.YSFPBL_ZY,b.YSFPBL_SS,b.YSFPBL_DS,b.YSFPBL_XQ,b.YSFPBL_XZ,b.YSFPBL_XC,b.ZZSNSRLX_DM,b.SKGK_DM,b.YHZL_DM,b.YH_DM,b.YHZH,b.ZFR_DM,b.ZFRQ,b.XHR_DM,b.XHRQ,b.THRQ,b.THRQ_JZ,b.JDXZ_DM,b.ZGSWRY_DM,b.SKSS_SWJG_DM,b.TTJG_DM,b.NSR_SWJG_DM,b.SWJG_DM,b.LRR_DM,b.LRRQ,b.XGR_DM,b.XGRQ, rownum r  ");
                    strSql.Append("     from ltais.dj_nsrxx a, ltais.sb_ttxx b  where " + orastrwhere2 + ")");
                    ds = DbHelperOra.Query(strSql.ToString());

                    SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);
                    sqlbulkcopy.DestinationTableName = "ltais.SB_TTXX";//数据库中的表名
                    sqlbulkcopy.BulkCopyTimeout = outTimes;
                    sqlbulkcopy.WriteToServer(ds.Tables[0]);
                }
                else
                {
                    if (sumCount > temp)
                    {
                        DataSet ds = new DataSet();
                        strSql = new StringBuilder();
                        strSql.Append("select TTXH,PZ_DM,XTSPHM,YSSPHM,KDTSKXH,WSPZXH,NSRDZDAH,SSSQ_Q,SSSQ_Z,KPRQ,ZSXM_DM,ZSPM_DM,SKZL_DM,SKSX_DM,SE,PZZL_DM,PZLRR_DM,TTLX_DM,JMYY_DM,JMYJ_DM,CKTSGC_DM,CKTS_QYLX_DM,HGDM,LHJHH,CKTS_ZDDH_BZ,TZLX_DM,TZRQ,DJZCLX_DM,HY_DM,YSKM_DM,YSFPBL_DM,YSFPBL_ZY,YSFPBL_SS,YSFPBL_DS,YSFPBL_XQ,YSFPBL_XZ,YSFPBL_XC,ZZSNSRLX_DM,SKGK_DM,YHZL_DM,YH_DM,YHZH,ZFR_DM,ZFRQ,XHR_DM,XHRQ,THRQ,THRQ_JZ,JDXZ_DM,ZGSWRY_DM,SKSS_SWJG_DM,TTJG_DM,NSR_SWJG_DM,SWJG_DM,LRR_DM,LRRQ,XGR_DM,XGRQ from (select b.TTXH,b.PZ_DM,b.XTSPHM,b.YSSPHM,b.KDTSKXH,b.WSPZXH,b.NSRDZDAH,b.SSSQ_Q,b.SSSQ_Z,b.KPRQ,b.ZSXM_DM,b.ZSPM_DM,b.SKZL_DM,b.SKSX_DM,b.SE,b.PZZL_DM,b.PZLRR_DM,b.TTLX_DM,b.JMYY_DM,b.JMYJ_DM,b.CKTSGC_DM,b.CKTS_QYLX_DM,b.HGDM,b.LHJHH,b.CKTS_ZDDH_BZ,b.TZLX_DM,b.TZRQ,b.DJZCLX_DM,b.HY_DM,b.YSKM_DM,b.YSFPBL_DM,b.YSFPBL_ZY,b.YSFPBL_SS,b.YSFPBL_DS,b.YSFPBL_XQ,b.YSFPBL_XZ,b.YSFPBL_XC,b.ZZSNSRLX_DM,b.SKGK_DM,b.YHZL_DM,b.YH_DM,b.YHZH,b.ZFR_DM,b.ZFRQ,b.XHR_DM,b.XHRQ,b.THRQ,b.THRQ_JZ,b.JDXZ_DM,b.ZGSWRY_DM,b.SKSS_SWJG_DM,b.TTJG_DM,b.NSR_SWJG_DM,b.SWJG_DM,b.LRR_DM,b.LRRQ,b.XGR_DM,b.XGRQ, rownum r  ");
                        strSql.Append("     from ltais.dj_nsrxx a, ltais.sb_ttxx b where " + orastrwhere2 + ") where r >=" + (temp + 1) + " and r <=" + (sumCount));
                        ds = DbHelperOra.Query(strSql.ToString());

                        SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, tran);
                        sqlbulkcopy.DestinationTableName = "ltais.SB_TTXX";//数据库中的表名
                        sqlbulkcopy.BulkCopyTimeout = outTimes;
                        sqlbulkcopy.WriteToServer(ds.Tables[0]);
                    }
                }

                strSql = new StringBuilder();
                strSql.Append(@"select sum(b.se) ");
                strSql.Append("  from ltais.dj_nsrxx a, ltais.sb_ttxx b where " + orastrwhere2);
                object ttsum = DbHelperOra.GetSingle(strSql.ToString());

                strSql = new StringBuilder();
                strSql.Append(@"select sum(se) ");
                strSql.Append(" from  ltais.SB_TTXX where " + strwhere2);
                // object ttsum2 = DbHelperSQL.GetSingle(strSql.ToString());
                command = new SqlCommand(strSql.ToString(), conn, tran);
                command.CommandTimeout = outTimes;
                object ttsum2 = command.ExecuteScalar();

                if (sum != null && sum2 != null)
                {
                    if (decimal.Parse(sum.ToString()) != decimal.Parse(sum2.ToString()))
                    {
                        throw new Exception("税款总额不一致");
                    }

                }
                else
                {
                    throw new Exception("税款总额出错");
                }

                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();

                throw new Exception(ex.Message + " 表名：" + a + "数量：" + teml2);
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 获取Excel中的数据
        /// </summary>
        /// <param name="ExcelFile"></param>
        /// <returns></returns>
        public DataSet ReadExcelTitle(string ExcelFile)
        {
            return DbHelperExcel.ReadExcelTitle(ExcelFile);
        }

        public DataSet GetAddCredits(string strWhere1)
        {
            return DbHelperSQL.Query(strWhere1.ToString());
        }

//        public DataSet GetAddCredits(string strWhere1, string strWhere2, string strWhere3, string fyear)
//        {
//            StringBuilder strSql = new StringBuilder();
//            if (fyear != "")
//            {
//                strSql.Append(@"
//SELECT z.*,x.NSRSBH,x.NSRMC,x.NSRBM,w.NSRDZDAH,w.FTaxClassAllowanceNo,w.sums FROM (
//SELECT e.NSRDZDAH,(ISNULL(e.sums,0)-ISNULL(f.sums,0)) AS AddCredits FROM (
//SELECT DISTINCT NSRDZDAH,SUM(FAdjustAmount) AS sums FROM fund.v_TaxAuditEntry a LEFT JOIN fund.t_TaxAuditForm b ON a.FTaxAuditId = b.FTaxAuditId AND a.FYear = b.FYear
//WHERE b.FAllowanceClassNo='J' AND a.FYear='" + fyear + @"'
//GROUP BY NSRDZDAH ,a.FTaxAuditId
//) AS e LEFT JOIN (
//SELECT DISTINCT NSRDZDAH,SUM(FAdjustAmount) AS sums FROM fund.v_TaxAuditEntry a LEFT JOIN fund.t_TaxAuditForm b ON a.FTaxAuditId = b.FTaxAuditId AND a.FYear = b.FYear
//WHERE b.FAllowanceClassNo='J' AND a.FYear='" + (decimal.Parse(fyear) - 1) + @"'
//GROUP BY NSRDZDAH,a.FTaxAuditId) AS f ON e.NSRDZDAH = f.NSRDZDAH) AS z LEFT JOIN 
//fund.V_DJ_NSRXX x ON z.NSRDZDAH = x.NSRDZDAH
//LEFT JOIN (
//SELECT DISTINCT b.NSRDZDAH,FTaxClassAllowanceNo,SUM(FAdjustAmount) AS sums FROM fund.v_TaxAuditEntry a LEFT JOIN fund.t_TaxAuditForm b ON a.FTaxAuditId = b.FTaxAuditId AND a.FYear = b.FYear
//WHERE b.FAllowanceClassNo='J' AND a.FYear='" + fyear + @"' GROUP BY  b.NSRDZDAH,FTaxClassAllowanceNo,a.FTaxAuditId
//) AS w ON z.NSRDZDAH=w.NSRDZDAH ");
//                if (strWhere.Trim() != "")
//                {
//                    strSql.Append(" where " + strWhere);
//                }
//            }
//            return DbHelperSQL.Query(strSql.ToString());
//        }
        public DataSet GetZZSYDB(string dtime, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            if (!string.IsNullOrEmpty(dtime))
            {
                strSql.Append(@"declare @dtime DATETIME
SET @dtime ='" + dtime + @"'
SELECT a.NSRBM,a.NSRDZDAH,a.NSRSBH,a.NSRMC, ISNULL(f.se1,0) AS se1,ISNULL( b.se2,0) AS se2,CASE b.se2 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(f.se1,0) AS DECIMAL)- b.se2)/ b.se2*100 ) END AS montb,ISNULL(c.se3,0) AS se3,ISNULL(d.se4,0) AS se4,
CASE d.se4 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(c.se3,0) AS DECIMAL)- d.se4)/ d.se4*100)  END AS yrtb,ISNULL(e.se5,0) AS se5 FROM (
 ltais.DJ_NSRXX a LEFT join
(SELECT ISNULL( sum(SE),0) AS se1,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime )=0 AND ZSXM_DM='01' GROUP BY NSRDZDAH
  ) f  on a.NSRDZDAH=f.NSRDZDAH
   LEFT JOIN
 (SELECT ISNULL( sum(SE),0) AS se2,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE  DATEDIFF(MM,RKRQ, DATEADD(YEAR,-1,@dtime ))=0 AND ZSXM_DM='01' GROUP BY NSRDZDAH
  )b ON  a.NSRDZDAH=b.NSRDZDAH
  LEFT JOIN
 ( SELECT  ISNULL( sum(SE),0) AS se3,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime)>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,@dtime  ),0 ))<=0 AND ZSXM_DM='01' GROUP BY NSRDZDAH
  ) c ON c.NSRDZDAH = a.NSRDZDAH
  LEFT JOIN
  (SELECT  ISNULL( sum(SE),0) AS se4,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,DATEADD(YEAR,-1,@dtime ))>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,DATEADD(YEAR,-1,@dtime ) ),0 ))<=0  AND ZSXM_DM='01' GROUP BY NSRDZDAH
  ) d ON d.NSRDZDAH = a.NSRDZDAH
  
  LEFT JOIN
  (SELECT  SUM(SE) AS se5,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(YEAR,RKRQ,DATEADD(YEAR,-1,@dtime ))=0 AND ZSXM_DM='01' GROUP BY NSRDZDAH
  )  e ON e.NSRDZDAH = a.NSRDZDAH)  WHERE   a.NSRDZDAH IN (SELECT NSRDZDAH FROM fund.t_Key_NSRXX  WHERE FReportName='4' )
 
 
                   ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" and " + strWhere);
                }

            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetQueryTB(string dtime, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            if (!string.IsNullOrEmpty(dtime))
            {
                strSql.Append(@"declare @dtime DATETIME
SET @dtime ='" + dtime + @"'
SELECT a.NSRBM,a.NSRDZDAH,a.NSRSBH,a.NSRMC, ISNULL(f.se1,0) AS se1,ISNULL( c.se2,0) AS se2,ISNULL(b.se3,0) AS se3,ISNULL(d.se4,0) AS se4,CASE b.se3 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(f.se1,0) AS DECIMAL)- b.se3)/ b.se3*100 ) END AS montb1,
CASE d.se4 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(c.se2,0) AS DECIMAL)- d.se4)/ d.se4*100)  END AS yrtb1 

, ISNULL(fa.sea1,0) AS sea1,ISNULL( ca.sea2,0) AS sea2,ISNULL(ba.sea3,0) AS sea3,ISNULL(da.sea4,0) AS sea4,CASE ba.sea3 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(fa.sea1,0) AS DECIMAL)- ba.sea3)/ ba.sea3*100 ) END AS montba1,
CASE da.sea4 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(ca.sea2,0) AS DECIMAL)- da.sea4)/ da.sea4*100)  END AS yrtba1 

, ISNULL(fb.seb1,0) AS seb1,ISNULL( cb.seb2,0) AS seb2,ISNULL(bb.seb3,0) AS seb3,ISNULL(db.seb4,0) AS seb4,CASE bb.seb3 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(fb.seb1,0) AS DECIMAL)- bb.seb3)/ bb.seb3*100 ) END AS montbb1,
CASE db.seb4 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(cb.seb2,0) AS DECIMAL)- db.seb4)/ db.seb4*100)  END AS yrtbb1 

, ISNULL(fc.sec1,0) AS sec1,ISNULL( cc.sec2,0) AS sec2,ISNULL(bc.sec3,0) AS sec3,ISNULL(dc.sec4,0) AS sec4,CASE bc.sec3 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(fc.sec1,0) AS DECIMAL)- bc.sec3)/ bc.sec3*100 ) END AS montbc1,
CASE dc.sec4 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(cc.sec2,0) AS DECIMAL)- dc.sec4)/ dc.sec4*100)  END AS yrtbc1 

, ISNULL(fd.sed1,0) AS sed1,ISNULL( cd.sed2,0) AS sed2,ISNULL(bd.sed3,0) AS sed3,ISNULL(dd.sed4,0) AS sed4,CASE bd.sed3 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(fd.sed1,0) AS DECIMAL)- bd.sed3)/ bd.sed3*100 ) END AS montbd1,
CASE dd.sed4 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(cd.sed2,0) AS DECIMAL)- dd.sed4)/ dd.sed4*100)  END AS yrtbd1 

, ISNULL(fe.see1,0) AS see1,ISNULL( ce.see2,0) AS see2,ISNULL(be.see3,0) AS see3,ISNULL(de.see4,0) AS see4,CASE be.see3 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(fe.see1,0) AS DECIMAL)- be.see3)/ be.see3*100 ) END AS montbe1,
CASE de.see4 WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL(ce.see2,0) AS DECIMAL)- de.see4)/ de.see4*100)  END AS yrtbe1 

,
(ISNULL(f.se1,0)-ISNULL(fa.sea1,0)-ISNULL(fb.seb1,0)-ISNULL(fc.sec1,0)-ISNULL(fd.sed1,0)-ISNULL(fe.see1,0)) AS sef1,
(ISNULL( c.se2,0)-ISNULL( ca.sea2,0)-ISNULL( cb.seb2,0)-ISNULL( cc.sec2,0)-ISNULL( cd.sed2,0)-ISNULL( ce.see2,0))AS sef2,
(ISNULL( b.se3,0)-ISNULL( ba.sea3,0)-ISNULL( bb.seb3,0)-ISNULL( bb.seb3,0)-ISNULL( bd.sed3,0)-ISNULL( be.see3,0))AS sef3,
(ISNULL( d.se4,0)-ISNULL( da.sea4,0)-ISNULL( db.seb4,0)-ISNULL( dd.sed4,0)-ISNULL( dd.sed4,0)-ISNULL( de.see4,0))AS sef4
,CASE(ISNULL( b.se3,0)-ISNULL( ba.sea3,0)-ISNULL( bb.seb3,0)-ISNULL( bb.seb3,0)-ISNULL( bd.sed3,0)-ISNULL( be.see3,0))  WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL((ISNULL(f.se1,0)-ISNULL(fa.sea1,0)-ISNULL(fb.seb1,0)-ISNULL(fc.sec1,0)-ISNULL(fd.sed1,0)-ISNULL(fe.see1,0)),0) AS DECIMAL)- (ISNULL( b.se3,0)-ISNULL( ba.sea3,0)-ISNULL( bb.seb3,0)-ISNULL( bb.seb3,0)-ISNULL( bd.sed3,0)-ISNULL( be.see3,0)))/ (ISNULL( b.se3,0)-ISNULL( ba.sea3,0)-ISNULL( bb.seb3,0)-ISNULL( bb.seb3,0)-ISNULL( bd.sed3,0)-ISNULL( be.see3,0))*100 ) END AS montbf1,
CASE(ISNULL( d.se4,0)-ISNULL( da.sea4,0)-ISNULL( db.seb4,0)-ISNULL( dd.sed4,0)-ISNULL( dd.sed4,0)-ISNULL( de.see4,0)) WHEN NULL THEN NULL 
WHEN 0 THEN NULL 
ELSE FLOOR((CAST( ISNULL((ISNULL( c.se2,0)-ISNULL( ca.sea2,0)-ISNULL( cb.seb2,0)-ISNULL( cc.sec2,0)-ISNULL( cd.sed2,0)-ISNULL( ce.see2,0)),0) AS DECIMAL)- (ISNULL( d.se4,0)-ISNULL( da.sea4,0)-ISNULL( db.seb4,0)-ISNULL( dd.sed4,0)-ISNULL( dd.sed4,0)-ISNULL( de.see4,0)))/ (ISNULL( d.se4,0)-ISNULL( da.sea4,0)-ISNULL( db.seb4,0)-ISNULL( dd.sed4,0)-ISNULL( dd.sed4,0)-ISNULL( de.see4,0))*100)  END AS yrtbf1 
FROM (
 ltais.DJ_NSRXX a LEFT join
(SELECT ISNULL( sum(SE),0) AS se1,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE ZSXM_DM IN (SELECT FTaxClassNo
  FROM fund.t_TaxClass where " + SZWHERE + @" ) and  DATEDIFF(MM,RKRQ,@dtime )=0  GROUP BY NSRDZDAH
  ) f  on a.NSRDZDAH=f.NSRDZDAH
  LEFT JOIN
   ( SELECT  ISNULL( sum(SE),0) AS se2,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE ZSXM_DM IN (SELECT FTaxClassNo
  FROM fund.t_TaxClass where " + SZWHERE + @" ) and DATEDIFF(MM,RKRQ,@dtime)>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,@dtime  ),0 ))<=0  GROUP BY NSRDZDAH
  ) c ON c.NSRDZDAH = a.NSRDZDAH
  
   LEFT JOIN
 (SELECT ISNULL( sum(SE),0) AS se3,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE ZSXM_DM IN (SELECT FTaxClassNo
  FROM fund.t_TaxClass where " + SZWHERE + @" ) and DATEDIFF(MM,RKRQ, DATEADD(YEAR,-1,@dtime ))=0 GROUP BY NSRDZDAH
  )b ON  a.NSRDZDAH=b.NSRDZDAH
  LEFT JOIN

  (SELECT  ISNULL( sum(SE),0) AS se4,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE ZSXM_DM IN (SELECT FTaxClassNo
  FROM fund.t_TaxClass where " + SZWHERE + @" ) and DATEDIFF(MM,RKRQ,DATEADD(YEAR,-1,@dtime ))>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,DATEADD(YEAR,-1,@dtime ) ),0 ))<=0   GROUP BY NSRDZDAH
  ) d ON d.NSRDZDAH = a.NSRDZDAH
    LEFT JOIN
  (SELECT ISNULL( sum(SE),0) AS sea1,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime )=0 AND ZSXM_DM='01' GROUP BY NSRDZDAH
  ) fa  on a.NSRDZDAH=fa.NSRDZDAH
  LEFT JOIN
   ( SELECT  ISNULL( sum(SE),0) AS sea2,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime)>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,@dtime  ),0 ))<=0 AND ZSXM_DM='01' GROUP BY NSRDZDAH
  ) ca ON ca.NSRDZDAH = a.NSRDZDAH
  
   LEFT JOIN
 (SELECT ISNULL( sum(SE),0) AS sea3,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE  DATEDIFF(MM,RKRQ, DATEADD(YEAR,-1,@dtime ))=0 AND ZSXM_DM='01' GROUP BY NSRDZDAH
  )ba ON  a.NSRDZDAH=ba.NSRDZDAH
  LEFT JOIN

  (SELECT  ISNULL( sum(SE),0) AS sea4,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,DATEADD(YEAR,-1,@dtime ))>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,DATEADD(YEAR,-1,@dtime ) ),0 ))<=0  AND ZSXM_DM='01' GROUP BY NSRDZDAH
  ) da ON da.NSRDZDAH = a.NSRDZDAH
   LEFT JOIN
   
 ( SELECT ISNULL( sum(SE),0) AS seb1,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime )=0 AND ZSXM_DM='03' GROUP BY NSRDZDAH
  ) fb  on a.NSRDZDAH=fb.NSRDZDAH
  LEFT JOIN
   ( SELECT  ISNULL( sum(SE),0) AS seb2,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime)>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,@dtime  ),0 ))<=0 AND ZSXM_DM='03' GROUP BY NSRDZDAH
  ) cb ON cb.NSRDZDAH = a.NSRDZDAH
  
   LEFT JOIN
 (SELECT ISNULL( sum(SE),0) AS seb3,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE  DATEDIFF(MM,RKRQ, DATEADD(YEAR,-1,@dtime ))=0 AND ZSXM_DM='03' GROUP BY NSRDZDAH
  )bb ON  a.NSRDZDAH=bb.NSRDZDAH
  LEFT JOIN

  (SELECT  ISNULL( sum(SE),0) AS seb4,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,DATEADD(YEAR,-1,@dtime ))>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,DATEADD(YEAR,-1,@dtime ) ),0 ))<=0  AND ZSXM_DM='03' GROUP BY NSRDZDAH
  ) db ON db.NSRDZDAH = a.NSRDZDAH
    LEFT JOIN
 ( SELECT ISNULL( sum(SE),0) AS sec1,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime )=0 AND ZSXM_DM='02' GROUP BY NSRDZDAH
  ) fc  on a.NSRDZDAH=fc.NSRDZDAH
  LEFT JOIN
   ( SELECT  ISNULL( sum(SE),0) AS sec2,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime)>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,@dtime  ),0 ))<=0 AND ZSXM_DM='02' GROUP BY NSRDZDAH
  ) cc ON cc.NSRDZDAH = a.NSRDZDAH
  
   LEFT JOIN
 (SELECT ISNULL( sum(SE),0) AS sec3,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE  DATEDIFF(MM,RKRQ, DATEADD(YEAR,-1,@dtime ))=0 AND ZSXM_DM='02' GROUP BY NSRDZDAH
  )bc ON  a.NSRDZDAH=bc.NSRDZDAH
  LEFT JOIN

  (SELECT  ISNULL( sum(SE),0) AS sec4,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,DATEADD(YEAR,-1,@dtime ))>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,DATEADD(YEAR,-1,@dtime ) ),0 ))<=0  AND ZSXM_DM='02' GROUP BY NSRDZDAH
  ) dc ON dc.NSRDZDAH = a.NSRDZDAH
     LEFT JOIN
 ( SELECT ISNULL( sum(SE),0) AS sed1,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime )=0 AND ZSXM_DM='04' GROUP BY NSRDZDAH
  ) fd  on a.NSRDZDAH=fd.NSRDZDAH
  LEFT JOIN
   ( SELECT  ISNULL( sum(SE),0) AS sed2,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime)>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,@dtime  ),0 ))<=0 AND ZSXM_DM='04' GROUP BY NSRDZDAH
  ) cd ON cd.NSRDZDAH = a.NSRDZDAH
  
   LEFT JOIN
 (SELECT ISNULL( sum(SE),0) AS sed3,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE  DATEDIFF(MM,RKRQ, DATEADD(YEAR,-1,@dtime ))=0 AND ZSXM_DM='04' GROUP BY NSRDZDAH
  )bd ON  a.NSRDZDAH=bd.NSRDZDAH
  LEFT JOIN

  (SELECT  ISNULL( sum(SE),0) AS sed4,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,DATEADD(YEAR,-1,@dtime ))>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,DATEADD(YEAR,-1,@dtime ) ),0 ))<=0  AND ZSXM_DM='04' GROUP BY NSRDZDAH
  ) dd ON dd.NSRDZDAH = a.NSRDZDAH
     LEFT JOIN
 ( SELECT ISNULL( sum(SE),0) AS see1,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime )=0 AND ZSXM_DM='06' GROUP BY NSRDZDAH
  ) fe  on a.NSRDZDAH=fe.NSRDZDAH
  LEFT JOIN
   ( SELECT  ISNULL( sum(SE),0) AS see2,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,@dtime)>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,@dtime  ),0 ))<=0 AND ZSXM_DM='06' GROUP BY NSRDZDAH
  ) ce ON ce.NSRDZDAH = a.NSRDZDAH
  
   LEFT JOIN
 (SELECT ISNULL( sum(SE),0) AS see3,NSRDZDAH
  
  FROM  fund.V_SB_ZSXX WHERE  DATEDIFF(MM,RKRQ, DATEADD(YEAR,-1,@dtime ))=0 AND ZSXM_DM='06' GROUP BY NSRDZDAH
  )be ON  a.NSRDZDAH=be.NSRDZDAH
  LEFT JOIN

  (SELECT  ISNULL( sum(SE),0) AS see4,NSRDZDAH
   
  FROM  fund.V_SB_ZSXX WHERE DATEDIFF(MM,RKRQ,DATEADD(YEAR,-1,@dtime ))>=0 AND DATEDIFF(MM,RKRQ, DATEADD(YEAR,DATEDIFF(YEAR,0,DATEADD(YEAR,-1,@dtime ) ),0 ))<=0  AND ZSXM_DM='06' GROUP BY NSRDZDAH
  ) de ON de.NSRDZDAH = a.NSRDZDAH
  )  WHERE   a.NSRDZDAH IN (SELECT NSRDZDAH FROM fund.t_Key_NSRXX WHERE FReportName='1')
 
 
                   
 
  ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" and " + strWhere);
                }

            }
            return DbHelperSQL.Query(strSql.ToString());

        }
 public DataSet GetQueryZD(string dtime, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            if (!string.IsNullOrEmpty(dtime))
            {
                strSql.Append(@"declare @dtime DATETIME
SET @dtime ='" + dtime + @"'
SELECT  ROW_NUMBER() OVER (ORDER BY a.NSRDZDAH ASC) AS xh, a.NSRMC ,
        a.NSRDZDAH ,a.NSRSBH,
        se1 AS jzs ,
        se2 AS xfs ,
        se3 AS yys ,
        se4 AS qys ,
        se5 AS grs ,
        se6 AS qts ,
        ( ISNULL(se1, 0) + ISNULL(se2, 0) + ISNULL(se3, 0) + ISNULL(se4, 0)
          + ISNULL(se5, 0) + ISNULL(se6, 0) ) AS xj ,
        se7 AS jyfj ,
        ( ISNULL(se1, 0) + ISNULL(se2, 0) + ISNULL(se3, 0) + ISNULL(se4, 0)
          + ISNULL(se5, 0) + ISNULL(se6, 0) + ISNULL(se7, 0) ) AS qb
FROM    ( SELECT    DJ_NSRXX.NSRDZDAH ,ltais.DJ_NSRXX.NSRSBH,
                    NSRMC
          FROM      fund.t_Key_NSRXX
                    INNER JOIN ltais.DJ_NSRXX ON DJ_NSRXX.NSRDZDAH = t_Key_NSRXX.NSRDZDAH
          WHERE     FReportName = '2'
        ) a
        LEFT JOIN ( SELECT  ISNULL(SUM(SE), 0) AS se1 ,
                            NSRDZDAH
                    FROM    fund.V_SB_ZSXX
                    WHERE   DATEDIFF(MM, RKRQ, @dtime) = 0
                            AND ZSXM_DM = '01'
                    GROUP BY NSRDZDAH
                  ) f ON a.NSRDZDAH = f.NSRDZDAH
        LEFT JOIN ( SELECT  ISNULL(SUM(SE), 0) AS se2 ,
                            NSRDZDAH
                    FROM    fund.V_SB_ZSXX
                    WHERE   DATEDIFF(MM, RKRQ, @dtime) = 0
                            AND ZSXM_DM = '03'
                    GROUP BY NSRDZDAH
                  ) b ON a.NSRDZDAH = b.NSRDZDAH
        LEFT JOIN ( SELECT  ISNULL(SUM(SE), 0) AS se3 ,
                            NSRDZDAH
                    FROM    fund.V_SB_ZSXX
                    WHERE   DATEDIFF(MM, RKRQ, @dtime) = 0
                            AND ZSXM_DM = '02'
                    GROUP BY NSRDZDAH
                  ) c ON a.NSRDZDAH = c.NSRDZDAH
        LEFT JOIN ( SELECT  ISNULL(SUM(SE), 0) AS se4 ,
                            NSRDZDAH
                    FROM    fund.V_SB_ZSXX
                    WHERE   DATEDIFF(MM, RKRQ, @dtime) = 0
                            AND ZSXM_DM = '04'
                    GROUP BY NSRDZDAH
                  ) d ON a.NSRDZDAH = d.NSRDZDAH
        LEFT JOIN ( SELECT  ISNULL(SUM(SE), 0) AS se5 ,
                            NSRDZDAH
                    FROM    fund.V_SB_ZSXX
                    WHERE   DATEDIFF(MM, RKRQ, @dtime) = 0
                            AND ZSXM_DM = '06'
                    GROUP BY NSRDZDAH
                  ) e ON a.NSRDZDAH = e.NSRDZDAH
        LEFT JOIN ( SELECT  ISNULL(SUM(SE), 0) AS se6 ,
                            NSRDZDAH
                    FROM    fund.V_SB_ZSXX
                    WHERE   DATEDIFF(MM, RKRQ, @dtime) = 0
                            AND ZSXM_DM NOT IN ( '01', '02', '03', '04', '06',
                                                 '61' )
                                and ZSXM_DM IN (SELECT FTaxClassNo
  FROM fund.t_TaxClass where " + SZWHERE + @" )
                    GROUP BY NSRDZDAH
                  ) g ON a.NSRDZDAH = g.NSRDZDAH
        LEFT JOIN ( SELECT  ISNULL(SUM(SE), 0) AS se7 ,
                            NSRDZDAH
                    FROM    fund.V_SB_ZSXX
                    WHERE   DATEDIFF(MM, RKRQ, @dtime) = 0
                            AND ZSXM_DM = '61'
                    GROUP BY NSRDZDAH
                  ) h ON a.NSRDZDAH = h.NSRDZDAH");
   if (strWhere.Trim() != "")
                {
                    strSql.Append(" and " + strWhere);
                }

            }
            return DbHelperSQL.Query(strSql.ToString());

        }
  
    }
}
